<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Getting database metadata</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="PHP, PostgreSQL, database, metadata, tutorial, learn PostgreSQL PHP ">
<meta name="description" content="In this part of the PostgreSQL PHP tutorial, we work 
with database metadata.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">

<div>
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

</div>


<div class="content2">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Getting database metadata</h1>

<p>
Metadata is information about the data in the database. 
Metadata in PostgreSQL contains information about the tables 
and columns, in which we store data. Number of rows affected 
by an SQL statement is metadata. Number of rows and columns returned 
in a result set belong to metadata as well.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
There is an experimental function <code>pg_meta_data()</code>, which returns 
table definition for a table name as an array.
</p>


<h2>Columns, rows</h2>

<p>
As we have already stated, the number of columns and rows in a result
set is considered to be metadata. 
</p>

<pre class="code">
&lt;?php 

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
    or die ("Could not connect to server\n"); 

$query = "SELECT Name, Price FROM Cars LIMIT 4"; 
$rs = pg_query($con, $query) or die (pg_last_error($con)); 

$num_rows = pg_num_rows($rs);
$num_cols = pg_num_fields($rs);

echo "There are $num_rows rows and $num_cols columns in the query\n";

pg_close($con); 

?&gt;
</pre>

<p>
In the above example, we get the number of rows and columns returned
by a query. 
</p>

<pre class="explanation">
$query = "SELECT Name, Price FROM Cars LIMIT 4"; 
</pre>

<p>
From the SQL query we can see, that we select 2 columns and 4 rows.
The query could be created also dynamically.
</p>

<pre class="explanation">
$num_rows = pg_num_rows($rs);
$num_cols = pg_num_fields($rs);
</pre>

<p>
The <code>pg_num_rows()</code> function returns the number of rows in a 
PostgreSQL result resource. The <code>pg_num_rows()</code> function 
returns the number of columns (fields) in a PostgreSQL result resource.
</p>

<pre>
$ php colsrows.php
There are 4 rows and 2 columns in the query.
</pre>

<p>
Example output.
</p>


<h2>Column headers</h2>

<p>
Next we will show, how to print column headers with the data
from a database table.
</p>

<pre class="code">
&lt;?php 

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
    or die ("Could not connect to server\n"); 

$query = "SELECT id, name, price FROM cars LIMIT 5"; 
$rs = pg_query($con, $query) or die (pg_last_error($con)); 

$fname1 = pg_field_name($rs, 0);
$fname2 = pg_field_name($rs, 1);
$fname3 = pg_field_name($rs, 2);

printf("%3s  %-10s %8s\n", $fname1, $fname2, $fname3);

while ($row = pg_fetch_row($rs)) {
  printf("%3s  %-10s %8s\n", $row[0], $row[1], $row[2]);
}

pg_close($con); 

?&gt;
</pre>

<p>
In this program, we select 5 rows from the cars table
with the column names. 
</p>

<pre class="explanation">
$fname1 = pg_field_name($rs, 0);
$fname2 = pg_field_name($rs, 1);
$fname3 = pg_field_name($rs, 2);
</pre>

<p>
The <code>pg_field_name()</code> function returns the name of
the column (field) for the specified column number.
</p>


<pre class="explanation">
printf("%3s  %-10s %8s\n", $fname1, $fname2, $fname3);
</pre>

<p>
We print the column headers. We do some formatting with the
printf function.
</p>

<pre>
$ php column_headers.php 
 id  name          price
  1  Audi          52642
  2  Mercedes      57127
  3  Skoda          9000
  4  Volvo         29000
  5  Bentley      350000
</pre>

<p>
Ouput of the program. 
</p>


<h2>Affected rows</h2>

<p>
In the following example, we will find out how many changes have
been done by a particular SQL command.
</p>

<pre class="code">
&lt;?php 

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
    or die ("Could not connect to server\n"); 

$query = "DROP TABLE IF EXISTS friends"; 
pg_query($con, $query) or die("Cannot execute query: $query\n");

$query = "CREATE TABLE friends(id INT, name TEXT)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n");

$query = "INSERT INTO friends VALUES (1, 'Jane'), (2, 'Thomas')"
    . ", (3, 'Beky'), (4, 'Robert'), (5, 'Lucy')"; 
$res = pg_query($con, $query) or die("Cannot execute query: $query\n");

$ar = pg_affected_rows($res);
echo "The query has affected $ar rows\n";

$query = "DELETE FROM friends WHERE id IN (3, 4, 5)";
$res = pg_query($con, $query) or die("Cannot execute query: $query\n");

$ar = pg_affected_rows($res);
echo "The query has affected $ar rows\n";

pg_close($con);

?&gt;
</pre>

<p>
We create a friends table. In the last SQL command, we delete three
rows. We have one INSERT and one DELETE statement for which we can
call the <code>pg_affected_rows()</code> to get the number of affected
rows. 
</p>

<pre class="explanation">
$query = "INSERT INTO friends VALUES (1, 'Jane'), (2, 'Thomas')"
    . ", (3, 'Beky'), (4, 'Robert'), (5, 'Lucy')"; 
</pre>

<p>
We insert five rows into the friends table.
</p>

<pre class="explanation">
$ar = pg_affected_rows($res);
echo "The query has affected $ar rows\n";
</pre>

<p>
The <code>pg_affected_rows()</code> function returns the number of
rows affected by the last SQL statement. 
</p>

<pre>
$ php affected_rows.php
The query has affected 5 rows
The query has affected 3 rows
</pre>

<p>
The INSERT statement has created five rows, the DELETE statement has removed
3 rows. 
</p>


<h2>Table metadata</h2>

<p>
There is an experimental <code>pg_meta_data()</code>. 
It returns metadata for each column of a database table.
</p>

<pre class="code">
&lt;?php 

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
    or die ("Could not connect to server\n"); 

$ary = pg_meta_data($con, 'cars');
var_dump($ary);

pg_close($con); 

?&gt;
</pre>

<p>
The example prints metadata about table columns of the cars table. 
</p>

<pre class="explanation">
$ary = pg_meta_data($con, 'cars');
</pre>

<p>
The <code>pg_meta_data()</code> returns metadata information for the
cars table. It returns an array.
</p>

<pre class="explanation">
var_dump($ary);
</pre>

<p>
The <code>var_dump()</code> function dumps information about a variable.
In our case it is the returned array of metadata information.
</p>

<pre>
$ php metadata.php
array(3) {
  ["id"]=>
  array(6) {
    ["num"]=>
    int(1)
    ["type"]=>
    string(4) "int4"
    ["len"]=>
    int(4)
    ["not null"]=>
    bool(true)
...
</pre>

<p>
Excerpt from the example output.
</p>

<p>
In this part of the PostgreSQL PHP tutorial, we have worked with database
metadata. 
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified May 4, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

